package com.googlecode.geoteacher.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.googlecode.geoteacher.db.model.Challenge;
import com.googlecode.geoteacher.db.model.ChallengeResult;

public class DataSource {

	// Database fields
	private SQLiteDatabase database;
	private DatabaseOpenHelper dbHelper;

	public DataSource(Context context) {
		dbHelper = new DatabaseOpenHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}

	public Challenge createChallenge(Challenge challenge) {
		Log.i(DataSource.class.getName(), "Challenge created: " + challenge);
		ContentValues values = new ContentValues();
		values.put(DatabaseContract.ChallengeEntry.COLUMN_HASH,
				challenge.getHash());
		values.put(DatabaseContract.ChallengeEntry.COLUMN_NAME,
				challenge.getName());
		values.put(DatabaseContract.ChallengeEntry.COLUMN_PATH,
				challenge.getPath());

		long insertId = database.insert(
				DatabaseContract.ChallengeEntry.TABLE_NAME, null, values);

		if (insertId == -1) {
			return null;
		} else {
			challenge.setId(insertId);
			return challenge;
		}
	}

	public boolean deleteChallenge(Challenge challenge) {
		return deleteChallenge(challenge.getId());
	}

	public boolean deleteChallenge(long challengeId) {
		Log.i(DataSource.class.getName(), "Challenge deleted with id: "
				+ challengeId);
		int affected = database
				.delete(DatabaseContract.ChallengeEntry.TABLE_NAME,
						DatabaseContract.ChallengeEntry._ID + " = "
								+ challengeId, null);

		return affected == 1;
	}

	public boolean updateChallenge(Challenge challenge) {
		Log.i(DataSource.class.getName(), "Update challenge: " + challenge);
		ContentValues values = new ContentValues();
		values.put(DatabaseContract.ChallengeEntry.COLUMN_HASH,
				challenge.getHash());
		values.put(DatabaseContract.ChallengeEntry.COLUMN_NAME,
				challenge.getName());
		values.put(DatabaseContract.ChallengeEntry.COLUMN_PATH,
				challenge.getPath());
		int affected = database.update(
				DatabaseContract.ChallengeEntry.TABLE_NAME, values,
				DatabaseContract.ChallengeEntry._ID + "=" + challenge.getId(),
				null);

		return affected == 1;
	}

	public boolean updateChallengeHashIfNeeded(long challengeId, String actualHash) {
		Log.i(DataSource.class.getName(), "Update challenge hash if needed: "+challengeId);

		ContentValues values = new ContentValues();
		values.put(DatabaseContract.ChallengeEntry.COLUMN_HASH, actualHash);

		int affected = database
				.update(DatabaseContract.ChallengeEntry.TABLE_NAME, values,
						DatabaseContract.ChallengeEntry._ID + " = ? and "
								+ DatabaseContract.ChallengeEntry.COLUMN_HASH
								+ " <> ?",
						new String[] { String.valueOf(challengeId), actualHash });

		return affected == 1;
	}

	private Challenge getChallengeWith(String selection, String[] selectionArgs) {
		Cursor cursor = database.query(
				DatabaseContract.ChallengeEntry.TABLE_NAME,
				DatabaseContract.ChallengeEntry.ALL_COLUMNS, selection,
				selectionArgs, null, null, null);

		Challenge challenge = null;
		if (cursor.moveToFirst()) {
			challenge = cursorToChallenge(cursor);
		}
		cursor.close();

		return challenge;
	}

	public Challenge getChallenge(long challengeId) {
		return getChallengeWith(DatabaseContract.ChallengeEntry._ID + "="
				+ challengeId, null);
	}

	public Challenge getChallengeByHash(String hash) {
		return getChallengeWith(DatabaseContract.ChallengeEntry.COLUMN_HASH
				+ "=?", new String[] { hash });
	}

	public List<Challenge> getAllChallenges() {
		List<Challenge> list = new ArrayList<Challenge>();

		Cursor cursor = database.query(
				DatabaseContract.ChallengeEntry.TABLE_NAME,
				DatabaseContract.ChallengeEntry.ALL_COLUMNS, null, null, null,
				null, null);

		while (cursor.moveToNext()) {
			Challenge challenge = cursorToChallenge(cursor);
			list.add(challenge);
		}
		// make sure to close the cursor
		cursor.close();
		return list;
	}

	public long countChallenges() {
		return DatabaseUtils.queryNumEntries(database,
				DatabaseContract.ChallengeEntry.TABLE_NAME);
	}

	private Challenge cursorToChallenge(Cursor cursor) {
		Challenge challenge = new Challenge();
		challenge.setId(cursor.getLong(0));
		challenge.setHash(cursor.getString(1));
		challenge.setName(cursor.getString(2));
		challenge.setPath(cursor.getString(3));
		return challenge;
	}

	public ChallengeResult createChallengeResult(ChallengeResult result) {
		Log.i(DataSource.class.getName(), "ChallengeResult created: " + result);
		ContentValues values = new ContentValues();
		values.put(DatabaseContract.ChallengeResultEntry.COLUMN_CHALLENGE_ID,
				result.getChallengeId());
		values.put(DatabaseContract.ChallengeResultEntry.COLUMN_DISTANCE,
				result.getDistance());
		values.put(DatabaseContract.ChallengeResultEntry.COLUMN_CREATED, result
				.getCreated().getTime());
		long insertId = database.insert(
				DatabaseContract.ChallengeResultEntry.TABLE_NAME, null, values);

		if (insertId == -1) {
			return null;
		} else {
			result.setId(insertId);
			return result;
		}
	}

	public boolean deleteChallengeResult(ChallengeResult result) {
		return deleteChallengeResult(result.getId());
	}

	public boolean deleteChallengeResult(long resultId) {
		Log.i(DataSource.class.getName(), "ChallengeResult deleted with id: "
				+ resultId);
		int affected = database.delete(
				DatabaseContract.ChallengeResultEntry.TABLE_NAME,
				DatabaseContract.ChallengeResultEntry._ID + " = " + resultId,
				null);

		return affected == 1;
	}

	public int deleteAllChallengeResultsOfChallenge(long challengeId) {
		Log.i(DataSource.class.getName(),
				"Delete ChallengeResults with challengeId: " + challengeId);

		return database.delete(
				DatabaseContract.ChallengeResultEntry.TABLE_NAME,
				DatabaseContract.ChallengeResultEntry.COLUMN_CHALLENGE_ID + "="
						+ challengeId, null);
	}

	public boolean updateChallengeResult(ChallengeResult result) {
		Log.i(DataSource.class.getName(), "Update ChallengeResult: " + result);
		ContentValues values = new ContentValues();
		values.put(DatabaseContract.ChallengeResultEntry.COLUMN_CHALLENGE_ID,
				result.getChallengeId());
		values.put(DatabaseContract.ChallengeResultEntry.COLUMN_DISTANCE,
				result.getDistance());
		values.put(DatabaseContract.ChallengeResultEntry.COLUMN_CREATED, result
				.getCreated().getTime());
		int affected = database.update(
				DatabaseContract.ChallengeResultEntry.TABLE_NAME,
				values,
				DatabaseContract.ChallengeResultEntry._ID + "="
						+ result.getId(), null);

		return affected == 1;
	}

	public List<ChallengeResult> getAllChallengeResultsOfChallenge(
			long challengeId, String orderBy, int limit) {
		return getAllChallengeResultsWith(
				DatabaseContract.ChallengeResultEntry.COLUMN_CHALLENGE_ID + "="
						+ challengeId, orderBy, String.valueOf(limit));
	}

	public List<ChallengeResult> getAllChallengeResultsOfChallenge(
			long challengeId) {
		return getAllChallengeResultsWith(
				DatabaseContract.ChallengeResultEntry.COLUMN_CHALLENGE_ID + "="
						+ challengeId, null, null);
	}

	public List<ChallengeResult> getAllChallengeResults() {
		return getAllChallengeResultsWith(null, null, null);
	}

	private List<ChallengeResult> getAllChallengeResultsWith(String selection,
			String orderBy, String limit) {
		List<ChallengeResult> list = new ArrayList<ChallengeResult>();

		Cursor cursor = database.query(
				DatabaseContract.ChallengeResultEntry.TABLE_NAME,
				DatabaseContract.ChallengeResultEntry.ALL_COLUMNS, selection,
				null, null, null, orderBy, limit);

		while (cursor.moveToNext()) {
			ChallengeResult pair = cursorToChallengeResult(cursor);
			list.add(pair);
		}
		// make sure to close the cursor
		cursor.close();
		return list;
	}

	private ChallengeResult cursorToChallengeResult(Cursor cursor) {
		ChallengeResult result = new ChallengeResult();
		result.setId(cursor.getLong(0));
		result.setChallengeId(cursor.getLong(1));
		result.setDistance(cursor.getInt(2));
		result.setCreated(new Date(cursor.getLong(3)));
		return result;
	}
}
